Imports System
Imports System.Data
Imports System.Threading
Imports Devv.Core.Utils

Public Class DataHandler : Implements IDisposable

#Region "Properties"

	Private _LogEnabled As Boolean
	Private _AutoClose As Boolean = True
	Private _ConnectionString As String
	Private _Provider As ProviderEnum

	Public Property LogEnabled() As Boolean
		Get
			Return _LogEnabled
		End Get
		Set(ByVal value As Boolean)
			_LogEnabled = value
		End Set
	End Property

	Public Property AutoClose() As Boolean
		Get
			Return _AutoClose
		End Get
		Set(ByVal value As Boolean)
			_AutoClose = value
		End Set
	End Property

	Public Property ConnectionString() As String
		Get
			Return _ConnectionString
		End Get
		Set(ByVal value As String)
			_ConnectionString = value
		End Set
	End Property

	Public Property Provider() As ProviderEnum
		Get
			Return _Provider
		End Get
		Set(ByVal value As ProviderEnum)
			_Provider = value
		End Set
	End Property

	Public ReadOnly Property ProviderString() As String
		Get
			Return System.Enum.GetName(GetType(ProviderEnum), Provider)
		End Get
	End Property

#End Region

#Region "IDb Properties"

	Private _objCommand As IDbCommand
	Private _objConnection As IDbConnection
	Private _objTransaction As IDbTransaction

	Public Property CommandObject() As IDbCommand
		Get
			Return _objCommand
		End Get
		Set(ByVal value As IDbCommand)
			_objCommand = value
		End Set
	End Property

	Public Property ConnectionObject() As IDbConnection
		Get
			Return _objConnection
		End Get
		Set(ByVal value As IDbConnection)
			_objConnection = value
		End Set
	End Property

	Public Property TransactionObject() As IDbTransaction
		Get
			Return _objTransaction
		End Get
		Set(ByVal value As IDbTransaction)
			_objTransaction = value
		End Set
	End Property

#End Region

#Region "Readonly Properties"

	Public ReadOnly Property ConnectionTimeout() As Integer
		Get
			If Not ConnectionObject Is Nothing Then
				Return ConnectionObject.ConnectionTimeout
			Else
				Return 0
			End If
		End Get
	End Property

	Public ReadOnly Property State() As ConnectionState
		Get
			If Not ConnectionObject Is Nothing Then
				Return ConnectionObject.State
			Else
				Return ConnectionState.Closed
			End If
		End Get
	End Property

	Public ReadOnly Property ServerVersion() As String
		Get
			Dim strConn As String = ConnectionString.ToLower()
			Select Case Provider
				Case UniDataTier.ProviderEnum.Odbc, UniDataTier.ProviderEnum.OleDb
					If strConn.Contains("mysql") Or strConn.Contains("myoledb") Then
						Return "MySQL"
					ElseIf strConn.Contains("oracle") Or strConn.Contains("msdaora") Then
						Return "Oracle"
					ElseIf strConn.Contains("msssql") Or strConn.Contains("sqlserver") Or strConn.Contains("sql server") Then
						Return "SQL Server"
					Else
						Return "?"
					End If
				Case UniDataTier.ProviderEnum.SqlServer
					Return "SQL Server " & CType(ConnectionObject, SqlClient.SqlConnection).ServerVersion
			End Select
			Return "?"
		End Get
	End Property

#End Region

#Region "Constructors and dispose"

	Public Sub New()

		Provider = Config.DefaultProvider
		ConnectionString = Config.DefaultConnectionString

		Me.Create()

	End Sub

	Public Sub New(ByVal strConnectionString As String)

		Provider = Config.DefaultProvider
		ConnectionString = strConnectionString

		Me.Create()

	End Sub

	Public Sub New(ByVal strConnectionString As String, ByVal objProvider As ProviderEnum)

		Provider = objProvider
		ConnectionString = strConnectionString

		Me.Create()

	End Sub

	Public Sub Dispose() Implements IDisposable.Dispose

		Me.Finalize()

	End Sub

	Protected Overrides Sub Finalize()

		Me.Close()
		MyBase.Finalize()

	End Sub

#End Region

#Region "Connection methods"

	Private Sub Create()

		LogEnabled = Config.LogEnabled

		Try

			If Provider = Nothing Then
				Throw New UniDataTierException(GetType(DataHandler), My.Resources.Messages.ErrorProviderNotSpecified)
			ElseIf ConnectionString.Length < 0 Then
				Throw New UniDataTierException(GetType(DataHandler), My.Resources.Messages.ErrorConnectionNotSpecified)
			End If

			Select Case Provider
				Case UniDataTier.ProviderEnum.SqlServer
					ConnectionObject = New SqlClient.SqlConnection(_ConnectionString)
					CommandObject = New SqlClient.SqlCommand()
				Case UniDataTier.ProviderEnum.Odbc
					ConnectionObject = New Odbc.OdbcConnection(_ConnectionString)
					CommandObject = New Odbc.OdbcCommand()
				Case Else
					ConnectionObject = New OleDb.OleDbConnection(_ConnectionString)
					CommandObject = New OleDb.OleDbCommand()
			End Select

		Catch ex As Exception

			If LogEnabled Then
				LogManager.AppendLog("Create", ProviderString & ": " & ConnectionString, GetExceptionMessage(ex))
			End If

			Return

		End Try

		If LogEnabled Then
			LogManager.AppendLog("Create", ProviderString & ": " & ConnectionString)
		End If

	End Sub

	Private Sub SetCommandType(ByVal strSql As String)

		If strSql.Contains("SELECT ") OrElse strSql.Contains("INSERT ") OrElse strSql.Contains("UPDATE ") OrElse strSql.Contains("DELETE ") Then
			CommandObject.CommandType = CommandType.Text
		Else
			CommandObject.CommandType = CommandType.StoredProcedure
		End If

		If LogEnabled Then
			LogManager.AppendLog("SetCommandType", System.Enum.GetName(GetType(CommandType), CommandObject.CommandType))
		End If

	End Sub

	Public Function TryOpen() As String

		Dim strReturn As String = String.Empty

		Try

			ConnectionObject.Open()

			If LogEnabled Then
				LogManager.AppendLog("TryOpen", ConnectionObject.ConnectionString)
			End If

		Catch ex As Exception

			strReturn = ex.Message

			If LogEnabled Then
				LogManager.AppendLog("TryOpen", ConnectionObject.ConnectionString, GetExceptionMessage(ex))
			End If

		End Try

		Return strReturn

	End Function

	Public Function Open() As Boolean

		Dim intRetry As Integer = 0

		TransactionObject = Nothing

		While (ConnectionObject.State = ConnectionState.Closed Or ConnectionObject.State = ConnectionState.Broken) AndAlso intRetry <= Config.MaxRetry

			Try

				ConnectionObject.Open()
				CommandObject.Connection = ConnectionObject

				If LogEnabled Then
					LogManager.AppendLog("Open", ConnectionObject.ConnectionString)
				End If

			Catch ex As Exception

				intRetry += 1

				If LogEnabled Then
					LogManager.AppendLog("Open (" & intRetry & ")", ConnectionObject.ConnectionString, GetExceptionMessage(ex))
				End If

				If intRetry = Config.MaxRetry Then
					ConnectionObject.ConnectionString &= ";Connection Timeout=" & New Random().Next(0, 100)
					Thread.Sleep(Config.RetryInterval)
				ElseIf intRetry < Config.MaxRetry Then
					Thread.Sleep(Config.RetryInterval)
				Else
					Throw
				End If

			End Try

		End While

		Return True

	End Function

	Public Function Close() As Boolean

		Dim result As Boolean

		If Not TransactionObject Is Nothing Then
			RollbackTransaction()
		End If

		If Not CommandObject Is Nothing Then
			CommandObject.Dispose()
			CommandObject = Nothing
		End If

		Try

			If Not ConnectionObject.State = ConnectionState.Closed Then
				ConnectionObject.Close()
				ConnectionObject.Dispose()
			End If

			If LogEnabled Then
				LogManager.AppendLog("Close", ConnectionObject.ConnectionString)
			End If

			result = True

		Catch ex As Exception

			If LogEnabled Then
				LogManager.AppendLog("Close", ConnectionObject.ConnectionString, GetExceptionMessage(ex))
			End If

			result = False

		Finally

			If Not ConnectionObject Is Nothing Then
				ConnectionObject.Dispose()
			End If

		End Try

		Return result

	End Function

#End Region

#Region "Transaction methods"

	Private Sub BeginTransaction()

		Return

		'TO BE IMPLEMENTED!!!
		'Try

		'	_objTransaction = _objConnection.BeginTransaction()
		'	_objCommand.Transaction = _objTransaction

		'	If DoLog Then
		'		LogManager.AppendLog("BeginTransaction")
		'	End If

		'Catch ex As Exception

		'	If DoLog Then
		'		LogManager.AppendLog("BeginTransaction", ex.Message)
		'	Else
		'		Throw ex
		'	End If

		'End Try

	End Sub

	Private Sub CommitTransaction()

		Return

		'TO BE IMPLEMENTED!!!
		'Try

		'	If Not _objTransaction Is Nothing Then

		'		_objTransaction.Commit()
		'		_objTransaction.Dispose()
		'		_objTransaction = Nothing

		'		If DoLog Then
		'			LogManager.AppendLog("CommitTransaction")
		'		End If

		'	Else

		'		Throw New UniDataTierException(GetType(DataHandler), Config.Resource.GetString("ErrorInvalidCommit"))

		'	End If

		'Catch ex As Exception

		'	If DoLog Then
		'		LogManager.AppendLog("CommitTransaction", ex.Message)
		'	Else
		'		Throw ex
		'	End If

		'End Try

	End Sub

	Private Sub RollbackTransaction()

		Return

		'TO BE IMPLEMENTED!!!
		'Try

		'	_objTransaction.Rollback()

		'	If DoLog Then
		'		LogManager.AppendLog("RollbackTransaction")
		'	End If

		'Catch ex As Exception

		'	If DoLog Then
		'		LogManager.AppendLog("RollbackTransaction", ex.Message)
		'	End If

		'	Throw

		'End Try

	End Sub

#End Region

#Region "Execution methods"

	Public Sub AddParameter(ByVal name As String, ByVal value As Object)

		AddParameter(name, value, False)

	End Sub

	Public Sub AddParameter(ByVal name As String, ByVal value As Object, ByVal emptyAsNull As Boolean)

		If emptyAsNull AndAlso Not DataUtil.HasValue(value) Then
			value = DBNull.Value
		End If

		Select Case _Provider
			Case UniDataTier.ProviderEnum.SqlServer
				CommandObject.Parameters.Add(New SqlClient.SqlParameter(name, value))
			Case UniDataTier.ProviderEnum.Oracle
				CommandObject.Parameters.Add(New OracleClient.OracleParameter(name, value))
			Case UniDataTier.ProviderEnum.Odbc
				CommandObject.Parameters.Add(New Odbc.OdbcParameter(name, value))
			Case Else
				CommandObject.Parameters.Add(New OleDb.OleDbParameter(name, value))
		End Select

		If LogEnabled Then
			LogManager.AppendLog("AddParameter", name & ": " & value.ToString())
		End If

	End Sub

	Public Function ExecuteReader(ByVal strSql As String) As IDataReader

		Dim objReturn As IDataReader

		If Not Me.Open() Then
			Return Nothing
		End If

		Try

			SetCommandType(strSql)
			CommandObject.CommandText = strSql
			BeginTransaction()

			If AutoClose Then
				objReturn = CommandObject.ExecuteReader(CommandBehavior.CloseConnection)
			Else
				objReturn = CommandObject.ExecuteReader()
			End If

			CommitTransaction()

			If LogEnabled Then
				LogManager.AppendLog("ExecuteReader", strSql)
			End If

		Catch ex As Exception

			If LogEnabled Then
				LogManager.AppendLog("ExecuteReader", strSql, GetExceptionMessage(ex))
			End If

			Throw

		End Try

		Return objReturn

	End Function

	Public Function ExecuteScalar(ByVal strSql As String) As Object

		Dim objReturn As Object

		If Not Me.Open() Then
			Return Nothing
		End If

		Try

			SetCommandType(strSql)
			CommandObject.CommandText = strSql
			BeginTransaction()

			objReturn = CommandObject.ExecuteScalar()

			CommitTransaction()

			If LogEnabled Then
				LogManager.AppendLog("ExecuteScalar", strSql)
			End If

		Catch ex As Exception

			If LogEnabled Then
				LogManager.AppendLog("ExecuteScalar", strSql, GetExceptionMessage(ex))
			End If

			Throw

		End Try

		If AutoClose Then
			Me.Close()
		End If

		Return objReturn

	End Function

	Public Function ExecuteNonQuery(ByVal strSql As String) As Integer

		Dim intReturn As Integer

		If Not Me.Open() Then
			Return 0
		End If

		Try

			SetCommandType(strSql)
			CommandObject.CommandText = strSql
			BeginTransaction()

			intReturn = CommandObject.ExecuteNonQuery()

			CommitTransaction()

			If LogEnabled Then
				LogManager.AppendLog("ExecuteNonQuery", strSql)
			End If

		Catch ex As Exception

			If LogEnabled Then
				LogManager.AppendLog("ExecuteNonQuery", strSql, GetExceptionMessage(ex))
			End If

			Throw

		End Try

		If AutoClose Then
			Me.Close()
		End If

		Return intReturn

	End Function

	Public Function ExecuteDataSet(ByVal strSql As String) As DataSet

		Return ExecuteDataSet(strSql, String.Empty)

	End Function

	Public Function ExecuteDataSet(ByVal strSql As String, ByVal strTable As String) As DataSet

		Dim objReturn As DataSet
		Dim objDataAdapter As IDbDataAdapter = Nothing

		If Not Me.Open() Then
			Return Nothing
		End If

		Try

			SetCommandType(strSql)
			CommandObject.CommandText = strSql
			BeginTransaction()

			Select Case _Provider
				Case ProviderEnum.SqlServer
					objDataAdapter = New SqlClient.SqlDataAdapter(CType(CommandObject, SqlClient.SqlCommand))
				Case ProviderEnum.Odbc
					objDataAdapter = New Odbc.OdbcDataAdapter(CType(CommandObject, Odbc.OdbcCommand))
				Case ProviderEnum.OleDb
					objDataAdapter = New OleDb.OleDbDataAdapter(CType(CommandObject, OleDb.OleDbCommand))
			End Select

			If strTable.Length > 0 Then
				objReturn = New DataSet(strTable)
			Else
				objReturn = New DataSet
			End If

			objDataAdapter.Fill(objReturn)

			CommitTransaction()

			If LogEnabled Then
				LogManager.AppendLog("ExecuteDataSet", strSql)
			End If

		Catch ex As Exception

			If LogEnabled Then
				LogManager.AppendLog("ExecuteDataSet", strSql, GetExceptionMessage(ex))
			End If

			Throw

		End Try

		If AutoClose Then
			Me.Close()
		End If

		Return objReturn

	End Function

	Public Function ExecuteDataSet(ByVal strSql() As String, ByVal strTable() As String) As DataSet

		Dim objReturn As DataSet
		Dim objDataAdapter As IDbDataAdapter
		Dim i As Integer
		Dim iLength As Integer

		If Not Me.Open() Then
			Return Nothing
		End If

		Try

			objReturn = New DataSet
			iLength = strSql.Length() - 1

			For i = 0 To iLength

				SetCommandType(strSql(i))
				CommandObject.CommandText = strSql(i)
				BeginTransaction()

				Select Case _Provider
					Case ProviderEnum.SqlServer
						objDataAdapter = New SqlClient.SqlDataAdapter(CType(CommandObject, SqlClient.SqlCommand))
						CType(objDataAdapter, SqlClient.SqlDataAdapter).Fill(objReturn, strTable(i))
					Case ProviderEnum.Odbc
						objDataAdapter = New Odbc.OdbcDataAdapter(CType(CommandObject, Odbc.OdbcCommand))
						CType(objDataAdapter, Odbc.OdbcDataAdapter).Fill(objReturn, strTable(i))
					Case ProviderEnum.OleDb
						objDataAdapter = New OleDb.OleDbDataAdapter(CType(CommandObject, OleDb.OleDbCommand))
						CType(objDataAdapter, OleDb.OleDbDataAdapter).Fill(objReturn, strTable(i))
				End Select

				CommitTransaction()

				CommandObject.Dispose()

				If LogEnabled Then
					LogManager.AppendLog("ExecuteDataSet", strSql(i))
				End If

			Next

		Catch ex As Exception

			If LogEnabled Then
				LogManager.AppendLog("ExecuteDataSet", strSql(i), GetExceptionMessage(ex))
			End If

			Throw

		End Try

		If AutoClose Then
			Me.Close()
		End If

		Return objReturn

	End Function

	Public Function GetInfo() As DbInfo

		Dim reader As IDataReader

		If Provider = ProviderEnum.SqlServer Then

			reader = ExecuteReader("sp_spaceused")

			Return New DbInfo(reader)

		End If

		Return New DbInfo()

	End Function

#End Region

#Region "Get values from data containers"

	Public Shared Function GetString(ByVal reader As IDataReader, ByVal column As String) As String

		Return GetString(reader, column, String.Empty)

	End Function

	Public Shared Function GetString(ByVal reader As IDataReader, ByVal column As String, ByVal defaultValue As String) As String

		Try

			Dim o As Object = reader(column)

			If DataUtil.HasValue(o) Then
				Return DataUtil.GetString(o)
			End If

			Return defaultValue

		Catch ex As Exception

			Return defaultValue

		End Try

	End Function

	Public Shared Function GetInt32(ByVal reader As IDataReader, ByVal column As String) As Integer

		Return GetInt32(reader, column, 0)

	End Function

	Public Shared Function GetInt32(ByVal reader As IDataReader, ByVal column As String, ByVal defaultValue As Integer) As Integer

		Try

			Dim o As Object = reader(column)

			If DataUtil.HasValue(o) Then
				Return DataUtil.GetInt32(o)
			End If

			Return defaultValue

		Catch ex As Exception

			Return defaultValue

		End Try

	End Function

	Public Shared Function GetDouble(ByVal reader As IDataReader, ByVal column As String) As Double

		Return GetDouble(reader, column, 0)

	End Function

	Public Shared Function GetDouble(ByVal reader As IDataReader, ByVal column As String, ByVal defaultValue As Double) As Double

		Try

			Dim o As Object = reader(column)

			If DataUtil.HasValue(o) Then
				Return DataUtil.GetDouble(o)
			End If

			Return defaultValue

		Catch ex As Exception

			Return defaultValue

		End Try

	End Function

	Public Shared Function GetBoolean(ByVal reader As IDataReader, ByVal column As String) As Boolean

		Return GetBoolean(reader, column, False)

	End Function

	Public Shared Function GetBoolean(ByVal reader As IDataReader, ByVal column As String, ByVal defaultValue As Boolean) As Boolean

		Try

			Dim o As Object = reader(column)

			If DataUtil.HasValue(o) Then
				Return DataUtil.GetBoolean(o)
			End If

			Return defaultValue

		Catch ex As Exception

			Return defaultValue

		End Try

	End Function

	Public Shared Function GetDateTime(ByVal reader As IDataReader, ByVal column As String) As DateTime

		Return GetDateTime(reader, column, DateTime.MinValue)

	End Function

	Public Shared Function GetDateTime(ByVal reader As IDataReader, ByVal column As String, ByVal defaultValue As DateTime) As DateTime

		Try

			Dim o As Object = reader(column)

			If DataUtil.HasValue(o) Then
				Return DataUtil.GetDateTime(o)
			End If

			Return defaultValue

		Catch ex As Exception

			Return defaultValue

		End Try

	End Function

#End Region

#Region "Helper methods"

	Public Shared Function CheckByteArrayToString(ByVal obj As Object) As String

		If obj.GetType Is GetType(Byte()) Then
			Return UniDataTier.Config.Encoding.GetString(CType(obj, Byte()))
		Else
			Return obj.ToString()
		End If

	End Function

	Public Shared Function HandleValue(ByVal strValue As String) As String

		If Config.LogEnabled Then
			LogManager.AppendLog("HandleValue (String)", strValue)
		End If

		If Not String.IsNullOrEmpty(strValue) Then
			Return "'" & strValue.Replace("'", "''") & "'"
		Else
			Return String.Empty
		End If

	End Function

	Public Shared Function HandleValue(ByVal intValue As Integer) As String

		If Config.LogEnabled Then
			LogManager.AppendLog("HandleValue (Integer)", intValue.ToString())
		End If

		Return intValue.ToString()

	End Function

	Public Shared Function HandleValue(ByVal intValue As Long) As String

		If Config.LogEnabled Then
			LogManager.AppendLog("HandleValue (Long)", intValue.ToString())
		End If

		Return intValue.ToString()

	End Function

	Public Shared Function HandleValue(ByVal intValue As Byte) As String

		If Config.LogEnabled Then
			LogManager.AppendLog("HandleValue (Byte)", intValue.ToString())
		End If

		Return intValue.ToString()

	End Function

	Public Shared Function HandleValue(ByVal blnValue As Boolean) As String

		If Config.LogEnabled Then
			LogManager.AppendLog("HandleValue (Boolean)", blnValue.ToString())
		End If

		If blnValue Then
			Return "1"
		Else
			Return "0"
		End If

	End Function

	Public Shared Function HandleValue(ByVal dtmValue As DateTime) As String

		If Config.LogEnabled Then
			LogManager.AppendLog("HandleValue (DateTime)", dtmValue.ToString())
		End If

		Return "'" & dtmValue.ToString("yyyy-MM-dd HH:mm:ss") & "'"

	End Function

	Public Shared Function HandleValue(ByVal value As String, ByVal strType As String) As String

		Dim strReturn As String

		If strType = String.Empty Then
			strType = "string"
		End If

		If String.IsNullOrEmpty(value) Then
			Return String.Empty
		End If

		Try

			Select Case strType.ToLower()
				Case "datetime"
					strReturn = "'" & DateTime.Parse(value).ToString("yyyy-MM-dd HH:mm:ss") & "'"
				Case "smalldatetime"
					strReturn = "'" & DateTime.Parse(value).ToString("yyyy-MM-dd HH:mm") & "'"
				Case "date"
					strReturn = "'" & DateTime.Parse(value).ToString("yyyy-MM-dd") & "'"
				Case "time"
					strReturn = "'" & DateTime.Parse(value).ToString("HH:mm:ss") & "'"
				Case "boolean", "bool", "bit"
					If Boolean.Parse(value) Then
						strReturn = "1"
					Else
						strReturn = "0"
					End If
				Case "integer", "int"
					strReturn = Integer.Parse(value).ToString()
				Case "double", "number", "decimal", "currency", "money"
					strReturn = Double.Parse(value).ToString()
				Case "string", "text"
					strReturn = "'" & value.Replace("'", "''") & "'"
				Case Else
					Throw New UniDataTierException(GetType(DataHandler), My.Resources.Messages.ErrorInvalidDataType & " " & strType)
			End Select

			If Config.LogEnabled Then
				LogManager.AppendLog("HandleValue (Object)", value.ToString())
			End If

		Catch ex As Exception

			If Config.LogEnabled Then
				LogManager.AppendLog("HandleValue (DateTime)", value.ToString(), GetExceptionMessage(ex))
			End If

			Throw

		End Try

		Return strReturn

	End Function

#End Region

#Region "Exception message"

	Private Shared Function GetExceptionMessage(ByVal ex As Exception) As String

		If ex Is Nothing Then
			Return String.Empty
		End If

		Dim message As String = ex.Message

		While Not ex.InnerException Is Nothing
			message &= Environment.NewLine & ex.InnerException.Message
			ex = ex.InnerException
		End While

		Return message

	End Function

#End Region

End Class